package levelrewind.android.app.model;

import java.util.HashMap;
import java.util.Map;

import levelrewind.android.app.model.Database;
import levelrewind.android.app.model.Util.Emotion;
import levelrewind.android.app.vo.Category;
import levelrewind.android.app.vo.Game;
import levelrewind.android.app.vo.Person;
import levelrewind.android.app.vo.PicturePath;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Class to internal database management
 * All tables has the same structure as tables in the external server
 * @author Sureewan, Martin
 *
 */
public class Database
{
	private static final String DATABASE_NAME = "appContainerDB";
	private static final int DATABASE_VERSION = 10;
	private DbHelper dbHelper;
	
	//Table Category
	private static final String CATEGORY_TABLE = "Category";
	private static final String CATEGORY_ID = "id";
	private static final String CATEGORY_NAME = "name";
	private static final String CATEGORY_DESCRIPTION = "description";
	
	//Table Person
	private static final String PERSON_TABLE = "Person";
	private static final String PERSON_ID = "id";
	private static final String PERSON_NAME = "name";
	private static final String PERSON_CATEGORY_ID = "category_id";
	private static final String PERSON_DESCRIPTION = "description";
	private static final String PERSON_SCORE = "score";
	private static final String PERSON_NUMOFFANS = "numOfFans";
	private static final String PERSON_ISCURRENTBATTLE = "isCurrentBattle";
	
	//Table Game
	private static final String GAME_TABLE = "Game";
	private static final String GAME_ID = "id";
	private static final String GAME_NAME = "name";
	private static final String GAME_DESCRIPTION = "description";
	private static final String GAME_PLAYED = "game_played";
	private static final String GAME_PUBLISHED = "published";
	private static final String GAME_ISEMBEDDED = "isEmbedded";
	private static final String GAME_PLATFORM= "platform";
	private static final String GAME_APPSTORE_LINK = "appStoreLink";
	private static final String GAME_GOOGLEPLAY_LINK = "googlePlayLink";
	private static final String GAME_ICON_PATH = "icon_path";
	
	//Table Emotion
	private static final String EMOTION_TABLE = "Emotion";
	private static final String EMOTION_ID = "id";
	private static final String EMOTION_NAME = "name";
	
	//Table Person_Emotion
	private static final String PERSON_EMOTION_TABLE = "Person_Emotion";
	private static final String PERSON_EMOTION_PERSON_ID = "person_id";
	private static final String PERSON_EMOTION_ID = "emotion_id";
	private static final String PERSON_EMOTION_PATH = "path";
	
	public static class DbHelper extends SQLiteOpenHelper
	{

		public DbHelper(Context context) 
		{			
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{
			db.execSQL(
					"CREATE TABLE IF NOT EXISTS "+CATEGORY_TABLE+" ( " +
						CATEGORY_ID+" INTEGER PRIMARY KEY, " +
						CATEGORY_NAME+" TEXT NOT NULL," +
						CATEGORY_DESCRIPTION+" TEXT NOT NULL);");
			
			db.execSQL("CREATE TABLE IF NOT EXISTS "+PERSON_TABLE+" (" +
						PERSON_ID+" INTEGER PRIMARY KEY NOT NULL, " +
						PERSON_NAME+" TEXT NOT NULL, " +
						PERSON_CATEGORY_ID+" INTEGER NOT NULL, " +
						PERSON_DESCRIPTION+" TEXT, " +
						PERSON_SCORE+" INTEGER, " +
						PERSON_NUMOFFANS+" INTEGER, " +
						PERSON_ISCURRENTBATTLE+" INTEGER, " +
						"FOREIGN KEY ("+PERSON_CATEGORY_ID+") REFERENCES "+CATEGORY_TABLE+"("+CATEGORY_ID+"));");
			
			db.execSQL("CREATE TABLE IF NOT EXISTS "+GAME_TABLE+" (" +
					GAME_ID+" INTEGER PRIMARY KEY NOT NULL, " +
					GAME_NAME+" TEXT NOT NULL, " +
					GAME_DESCRIPTION+" TEXT, " +
					GAME_PLAYED+" INTEGER, " +
					GAME_PUBLISHED+" DATE, " +
					GAME_ISEMBEDDED+" INTEGER, " +
					GAME_PLATFORM+" TEXT," +
					GAME_APPSTORE_LINK+" TEXT," +
					GAME_GOOGLEPLAY_LINK+" TEXT," +
					GAME_ICON_PATH+" TEXT);");
			
			db.execSQL(
					"CREATE TABLE IF NOT EXISTS "+EMOTION_TABLE+" ( " +
						EMOTION_ID+" INTEGER PRIMARY KEY, " +
						EMOTION_NAME+" TEXT NOT NULL);");
			
			db.execSQL(
					"CREATE TABLE IF NOT EXISTS "+PERSON_EMOTION_TABLE+" ( " +
						PERSON_EMOTION_PERSON_ID+" INTEGER , " +
						PERSON_EMOTION_ID+" INTEGER NOT NULL," +
						PERSON_EMOTION_PATH+" TEXT NOT NULL, " +
						"PRIMARY KEY ("+PERSON_EMOTION_PERSON_ID +","+PERSON_EMOTION_ID+"), "+
						"FOREIGN KEY ("+PERSON_EMOTION_ID+") REFERENCES "+EMOTION_TABLE+"("+EMOTION_ID+"));");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
		{
			db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + PERSON_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + GAME_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + EMOTION_TABLE);
			db.execSQL("DROP TABLE IF EXISTS " + PERSON_EMOTION_TABLE);
			onCreate(db);
		}
	}

	public SQLiteDatabase open()
	{
		return dbHelper.getWritableDatabase();
	}
	
	private SQLiteDatabase read()
	{
		return dbHelper.getReadableDatabase();
	}
	
	public Database(Context c)
	{
		dbHelper = new DbHelper(c);
	}
	
	public void close()
	{
		dbHelper.close();
	}
	
	public  void insertCategory(Category[] list) {
		
		SQLiteDatabase database = open();
		try {
			//Delete all data
			database.delete(CATEGORY_TABLE, null, null); 
			
			for (Category cat : list) {
				ContentValues cv = new ContentValues();
				cv.put(CATEGORY_ID, cat.getId());
				cv.put(CATEGORY_NAME, cat.getName());
				cv.put(CATEGORY_DESCRIPTION, cat.getDescription());
				
				database.insert(CATEGORY_TABLE, null, cv);
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
		
		close();
	}
	
	public Category[] getCategories() {
		SQLiteDatabase database = read();
		
		Category[] result = null;
		try {
			Cursor c = database.rawQuery("SELECT * FROM " + CATEGORY_TABLE, null);

			result = new Category[c.getCount()];
			int i = 0;
			for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
				Category cat = new Category();
				cat.setId(c.getInt(c.getColumnIndex(CATEGORY_ID)));
				cat.setName(c.getString(c.getColumnIndex(CATEGORY_NAME)));
				cat.setDescription(c.getString(c.getColumnIndex(CATEGORY_DESCRIPTION)));
				
				result[i++] = cat;
			}
			
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
    	close();
		return result;
	}
	
	public  void insertPerson(Person[] list) {
		SQLiteDatabase database = open();
		try {
			//Delete all data
			database.delete(PERSON_TABLE, null, null); 
			database.delete(PERSON_EMOTION_TABLE, null, null); 
			
			for (Person p : list) {
				ContentValues cv = new ContentValues();
				cv.put(PERSON_ID, p.getId());
				cv.put(PERSON_NAME, p.getName());
				cv.put(PERSON_CATEGORY_ID, p.getCategoryId());
				cv.put(PERSON_DESCRIPTION, p.getDescription());
				cv.put(PERSON_SCORE, p.getScore());
				cv.put(PERSON_NUMOFFANS, p.getNumOfFans());
				cv.put(PERSON_ISCURRENTBATTLE, p.getIsCurrentBattle());
				
				if (p.getPicPath() != null && p.getPicPath().size() != 0){
					insertPersonEmotion(database, p);
				}
				
				//Insert new data
				database.insert(PERSON_TABLE, null, cv);
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
		close();
	}
	
	private void insertEmotion(SQLiteDatabase database, PicturePath pp) {
		ContentValues cv = new ContentValues();
		cv.put(EMOTION_ID, pp.getEmotionId());
		cv.put(EMOTION_NAME, pp.getEmotionName());
		
		try {
			database.insertOrThrow(EMOTION_TABLE, null, cv);
		}
		catch(SQLException e)
		{
		}
	}
	
	private void insertPersonEmotion(SQLiteDatabase database, Person person) {
		try {
			for (PicturePath p : person.getPicPath().values()) {
				ContentValues cv = new ContentValues();
				cv.put(PERSON_EMOTION_PERSON_ID, person.getId());
				cv.put(PERSON_EMOTION_ID, p.getEmotionId());
				cv.put(PERSON_EMOTION_PATH, p.getPath());
				
				insertEmotion(database, p);
				
				//Insert new data
				database.insert(PERSON_EMOTION_TABLE, null, cv);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Person[] getPersons(int category_id) {
		SQLiteDatabase database = read();
		Person[] result = null;
		try {
			Cursor c = database.rawQuery("SELECT * FROM " + PERSON_TABLE + " WHERE "+PERSON_CATEGORY_ID+" = "+category_id+" ORDER BY " + PERSON_SCORE +" DESC", null);
			
			result = new Person[c.getCount()];
			int i = 0;
			for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
				Person p = new Person();
				p.setId(c.getInt(c.getColumnIndex(PERSON_ID)));
				p.setName(c.getString(c.getColumnIndex(PERSON_NAME)));
				p.setCategoryId(c.getInt(c.getColumnIndex(PERSON_CATEGORY_ID)));
				p.setDescription(c.getString(c.getColumnIndex(PERSON_DESCRIPTION)));
				p.setScore(c.getLong(c.getColumnIndex(PERSON_SCORE)));
				p.setNumOfFans(c.getLong(c.getColumnIndex(PERSON_NUMOFFANS)));
				p.setIsCurrentBattle(c.getInt(c.getColumnIndex(PERSON_ISCURRENTBATTLE)));

				Map<Emotion, PicturePath> picMap = new HashMap<Emotion, PicturePath>();
				
				Cursor c2 = database.rawQuery("SELECT p.*, e." +EMOTION_NAME+" as emotion_name FROM "+PERSON_EMOTION_TABLE+" p, "+EMOTION_TABLE
						+" e WHERE  p."+PERSON_EMOTION_ID+" = e."+EMOTION_ID+" AND p."+PERSON_EMOTION_PERSON_ID+"= "+p.getId()
						+" ORDER BY p." + PERSON_EMOTION_PERSON_ID +" DESC", null);
				
				for(c2.moveToFirst(); !c2.isAfterLast(); c2.moveToNext()) {
			        
			        PicturePath pp = new PicturePath();
			        pp.setEmotionId(c2.getInt(c2.getColumnIndex(PERSON_EMOTION_ID)));
			        pp.setEmotionName(c2.getString(c2.getColumnIndex("emotion_name")));
			        pp.setPath(c2.getString(c2.getColumnIndex(PERSON_EMOTION_PATH)));
			        
			        Emotion emotion = Util.getEmotionByName(pp.getEmotionName());
			        picMap.put(emotion, pp);	
	            }
	            
	            p.setPicPath(picMap);
	            
				result[i++] = p;
				c2.close();
			}
			
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
    	close();
		return result;
	}
	
	public Person getPerson(int per_id) {
		SQLiteDatabase database = read();
		Person p = null;
		try {
			Cursor c = database.rawQuery("SELECT * FROM " + PERSON_TABLE + " WHERE "+PERSON_ID+" = "+per_id, null);
			
			if (c.getCount() != 0)
			{
				c.moveToFirst();
				p = new Person();
				p.setId(c.getInt(c.getColumnIndex(PERSON_ID)));
				p.setName(c.getString(c.getColumnIndex(PERSON_NAME)));
				p.setCategoryId(c.getInt(c.getColumnIndex(PERSON_CATEGORY_ID)));
				p.setDescription(c.getString(c.getColumnIndex(PERSON_DESCRIPTION)));
				p.setScore(c.getLong(c.getColumnIndex(PERSON_SCORE)));
				p.setNumOfFans(c.getLong(c.getColumnIndex(PERSON_NUMOFFANS)));
				p.setIsCurrentBattle(c.getInt(c.getColumnIndex(PERSON_ISCURRENTBATTLE)));

				Map<Emotion, PicturePath> picMap = new HashMap<Emotion, PicturePath>();
				
				Cursor c2 = database.rawQuery("SELECT p.*, e." +EMOTION_NAME+" as emotion_name FROM "+PERSON_EMOTION_TABLE+" p, "+EMOTION_TABLE
						+" e WHERE  p."+PERSON_EMOTION_ID+" = e."+EMOTION_ID+" AND p."+PERSON_EMOTION_PERSON_ID+"= "+p.getId()
						+" ORDER BY p." + PERSON_EMOTION_PERSON_ID +" DESC", null);
				
				for(c2.moveToFirst(); !c2.isAfterLast(); c2.moveToNext()) {
			        
			        PicturePath pp = new PicturePath();
			        pp.setEmotionId(c2.getInt(c2.getColumnIndex(PERSON_EMOTION_ID)));
			        pp.setEmotionName(c2.getString(c2.getColumnIndex("emotion_name")));
			        pp.setPath(c2.getString(c2.getColumnIndex(PERSON_EMOTION_PATH)));
			        
			        Emotion emotion = Util.getEmotionByName(pp.getEmotionName());
			        picMap.put(emotion, pp);
	            }
	            
	            p.setPicPath(picMap);
	   
				c2.close();
			}
			
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
    	close();
		return p;
	}
	
	public boolean updatePersonScore(int person_id, long score) {
		try 
		{		
			SQLiteDatabase database = open();
			ContentValues cv = new ContentValues();

			cv.put(PERSON_SCORE, score);
			
			database.update(PERSON_TABLE, cv, PERSON_ID + "=?", new String [] { "" + person_id });
		
			database.close();
		}
		catch(SQLException ex)
		{
			return false;
		}
		return true;
	}
	
	public long getPersonScore(int person_id) {
		SQLiteDatabase database = read();
		long score = 0;
		try {
			Cursor c = database.rawQuery("SELECT score FROM " + PERSON_TABLE +" WHERE "+PERSON_ID +" = "+person_id, null);
			
			if (c.getCount() != 0)
			{
				c.moveToFirst();
				score = c.getLong(0);
			}
			
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
    	close();
    	
		return score;
	}
	
	public void insertGame(Game[] list) {
		SQLiteDatabase database = open();
		try {
			//Delete all data
			database.delete(GAME_TABLE, null, null); 
			
			for (Game g : list) {
				ContentValues cv = new ContentValues();
				cv.put(GAME_ID, g.getId());
				cv.put(GAME_NAME, g.getName());
				cv.put(GAME_DESCRIPTION, g.getDescription());
				cv.put(GAME_PLAYED, g.getGamePlayed());
				cv.put(GAME_PUBLISHED, g.getPublished());
				cv.put(GAME_ISEMBEDDED, g.getIsEmbedded());
				cv.put(GAME_PLATFORM, g.getPlatform());
				cv.put(GAME_APPSTORE_LINK, g.getAppStoreLink());
				cv.put(GAME_GOOGLEPLAY_LINK, g.getGooglePlayLink());
				cv.put(GAME_ICON_PATH, g.getIconPath());
				
				//Insert new data
				database.insert(GAME_TABLE, null, cv);
			}
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
		close();
	}
	
	public Game[] getGames() {
		SQLiteDatabase database = read();
		Game[] result = null;
		try {
			Cursor c = database.rawQuery("SELECT * FROM " + GAME_TABLE, null);
			
			result = new Game[c.getCount()];
			int i = 0;
			for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
				Game g = new Game();
				g.setId(c.getInt(c.getColumnIndex(GAME_ID)));
				g.setName(c.getString(c.getColumnIndex(GAME_NAME)));
				g.setDescription(c.getString(c.getColumnIndex(GAME_DESCRIPTION)));
				g.setGamePlayed(c.getLong(c.getColumnIndex(GAME_PLAYED)));
				g.setPublished(c.getString(c.getColumnIndex(GAME_PUBLISHED)));
				g.setIsEmbedded(c.getInt(c.getColumnIndex(GAME_ISEMBEDDED)));
				g.setPlatform(c.getString(c.getColumnIndex(GAME_PLATFORM)));
				g.setAppStoreLink(c.getString(c.getColumnIndex(GAME_APPSTORE_LINK)));
				g.setGooglePlayLink(c.getString(c.getColumnIndex(GAME_GOOGLEPLAY_LINK)));
				g.setIconPath(c.getString(c.getColumnIndex(GAME_ICON_PATH)));
				
				result[i++] = g;
			}
			
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			close();
		}
    	close();
		return result;
	}
}